﻿using System;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Text;

namespace Mind.Data
{
    /// <summary>
    /// Classe para fácil acesso a dados.
    /// </summary>
    public class DataAccess
    {
        private String _providerName;
        private String _connectionString = "";
        private DbProviderFactory _factory;
        private DataAccessTransaction _currentTransaction;
        public string ConnectionName { get; private set; }

        public DataAccessProvider Provider { get; protected set; }

        public String ProviderName
        {
            get
            {
                return _providerName;
            }
        }
        public String ConnectionString
        {
            get
            {
                return _connectionString;
            }
        }
        public DataAccessTransaction CurrentTransaction
        {
            get
            {
                return _currentTransaction;
            }
        }
        protected DbProviderFactory Factory
        {
            get
            {
                return _factory;
            }
        }

        private void SetProviderByName()
        {
            if (_providerName.IndexOf("MySQL", StringComparison.InvariantCultureIgnoreCase) >= 0)
                Provider = DataAccessProvider.MySQL;
            else if (_providerName.IndexOf("SQLite", StringComparison.InvariantCultureIgnoreCase) >= 0)
                Provider = DataAccessProvider.SQLite;
            else
                Provider = DataAccessProvider.SQLServer;
        }

        internal DataAccess(string connectionName)
        {
            string connectionId = "";
            _factory = GetFactory(connectionName, out _connectionString, out _providerName, out connectionId);
            SetProviderByName();
            this.ConnectionName = connectionId;

        }

        internal DataAccess(string name, DataAccessProvider provider, string connectionString)
        {
            this.ConnectionName = name;
            this.Provider = provider;

            switch (provider)
            {
                case DataAccessProvider.SQLite:
                    _providerName = "System.Data.SQLite";
                    break;
                case DataAccessProvider.MySQL:
                    _providerName = "MySql.Data.MySqlClient";
                    break;
                case DataAccessProvider.SQLServer:
                default:
                    _providerName = "System.Data.SqlClient";
                    break;
            }

            _factory = DbProviderFactories.GetFactory(_providerName);
            _connectionString = connectionString;
        }


        internal DataAccess(string name, DbProviderFactory provider, string connectionString)
        {
            this.ConnectionName = name;
            _providerName = provider.ToString();
            SetProviderByName();
            _factory = provider;
            _connectionString = connectionString;
        }

        public DataAccessTransaction CreateTransaction(IsolationLevel isolationLevel)
        {
            DbConnection conn = Factory.CreateConnection();
            conn.ConnectionString = ConnectionString;
            if (_currentTransaction == null || _currentTransaction.TransactionStatus != DataAccessTransaction.Status.Running)
                _currentTransaction = new DataAccessTransaction(conn, isolationLevel);

            return _currentTransaction;
        }


        public DbCommand CreateFormatCommand(string comando, params object[] parametros)
        {
            DbParameter[] result = new DbParameter[parametros.Length];
            for (int i = 0; i < parametros.Length; i++)
            {
                result[i] = CreateParameter(String.Format("{0:D3}", i), parametros[i]);
            }
            return CreateInlineCommand(comando, result);
        }

        public DbCommand CreateCommand(string comando, params IDataAccessParameter[] parametros)
        {
            DbParameter[] result = new DbParameter[parametros.Length];
            for (int i = 0; i < parametros.Length; i++)
            {
                if (String.IsNullOrEmpty(parametros[i].Name))
                    parametros[i].Name = String.Format("{0:D3}", i);
                result[i] = parametros[i].ToDbParameter();
            }
            return CreateInlineCommand(comando, result);
        }

        public DbCommand CreateInlineCommand(string comando, params DbParameter[] parametros)
        {
            comando = CommandFormat(comando, parametros);
            DbConnection conn = null;
            DbTransaction trans = null;
            if (_currentTransaction != null && _currentTransaction.TransactionStatus == DataAccessTransaction.Status.Running)
            {
                conn = _currentTransaction.Connection;
                trans = _currentTransaction.GetTransaction();
            }
            else
            {
                conn = Factory.CreateConnection();
                conn.ConnectionString = ConnectionString;
                conn.Open();
            }

            DbCommand cmd = Factory.CreateCommand();
            cmd.Connection = conn;
            cmd.Transaction = trans;
            cmd.CommandText = SqlSyntaxConverter.CheckSyntax(this.Provider, comando);
            cmd.Parameters.AddRange(parametros);

            return cmd;
        }

        public DbParameter CreateParameter()
        {
            return Factory.CreateParameter();
        }


        public DbParameter CreateParameter<T>(string name, T value)
        {
            return CreateParameter(name, value, MapColumnType(typeof(T)));
        }

        public DbParameter CreateParameter(string name, object value)
        {
            return CreateParameter(name, value, MapColumnType(value.GetType()));
        }

        public DbParameter CreateParameter(string name, object value, DbType type)
        {
            DbParameter param = this.CreateParameter();
            param.ParameterName = SqlSyntaxConverter.FormatParameterName(name, this.Provider);
            param.SourceColumn = name;
            param.Direction = ParameterDirection.Input;
            param.DbType = type;
            param.Value = (value == null ? DBNull.Value : value);
            return param;
        }

        private DbProviderFactory GetFactory(string connectionName, out string connectionString, out string providerName, out string connectionId)
        {
            string name = ConfigurationManager.AppSettings[connectionName];
            connectionId = name.Trim();

            if (connectionId.IndexOf('_') > 0)
            {
                connectionId = connectionId.Split(new char[] { '_' }, StringSplitOptions.RemoveEmptyEntries)[0];
            }

            ConnectionStringSettings connectionStringSettings = ConfigurationManager.ConnectionStrings[name];
            connectionString = connectionStringSettings.ConnectionString;
            providerName = connectionStringSettings.ProviderName;
            return DbProviderFactories.GetFactory(providerName);
        }

        #region Execute Non Query
        /// <summary>
        /// Executa um comando SQL.
        /// </summary>
        /// <param name="comando">O commando SQL</param>
        /// <param name="parametros">Os parametros do comando</param>
        /// <returns>O número de linhas afetadas</returns>
        public int ExecuteInlineNonQuery(string comando, params DbParameter[] parametros)
        {
            int result = 0;

            DbCommand cmd = CreateInlineCommand(comando, parametros);
            result = cmd.ExecuteNonQuery();
            return result;

        }

        /// <summary>
        /// Executa um comando SQL.
        /// </summary>
        /// <param name="comando">O commando SQL</param>
        /// <param name="parametros">Os parametros do comando</param>
        /// <returns>O número de linhas afetadas</returns>
        public int ExecuteNonQuery(string comando, params IDataAccessParameter[] parametros)
        {
            int result = 0;

            DbCommand cmd = CreateCommand(comando, parametros);
            result = cmd.ExecuteNonQuery();
            return result;

        }

        /// <summary>
        /// Executa um comando SQL.
        /// </summary>
        /// <param name="comando">O commando SQL</param>
        /// <param name="parametros">Os parametros do comando</param>
        /// <returns>O número de linhas afetadas</returns>
        public int ExecuteFormatNonQuery(string comando, params object[] parametros)
        {
            int result = 0;

            DbCommand cmd = CreateFormatCommand(comando, parametros);
            result = cmd.ExecuteNonQuery();
            return result;

        } 
        #endregion

        #region Execute Reader
        /// <summary>
        /// Executa uma Query SQL.
        /// </summary>
        /// <param name="comando">A Query SQL</param>
        /// <param name="parametros">Os parametros da Query</param>
        /// <returns>Um DataReader com os objetos retornados pela Query</returns>
        public DbDataReader ExecuteInlineReader(string comando, params DbParameter[] parametros)
        {
            DbDataReader result = null;
            DbCommand cmd = CreateInlineCommand(comando, parametros);
            result = cmd.ExecuteReader();
            return result;
        }

        /// <summary>
        /// Executa uma Query SQL.
        /// </summary>
        /// <param name="comando">A Query SQL</param>
        /// <param name="parametros">Os parametros da Query</param>
        /// <returns>Um DataReader com os objetos retornados pela Query</returns>
        public DbDataReader ExecuteReader(string comando, params IDataAccessParameter[] parametros)
        {
            DbDataReader result = null;
            DbCommand cmd = CreateCommand(comando, parametros);
            result = cmd.ExecuteReader();
            return result;
        }

        /// <summary>
        /// Executa uma Query SQL.
        /// </summary>
        /// <param name="comando">A Query SQL</param>
        /// <param name="parametros">Os parametros da Query</param>
        /// <returns>Um DataReader com os objetos retornados pela Query</returns>
        public DbDataReader ExecuteFormatReader(string comando, params object[] parametros)
        {
            DbDataReader result = null;
            DbCommand cmd = CreateFormatCommand(comando, parametros);
            result = cmd.ExecuteReader();
            return result;
        }

        #endregion

        #region Execute Scalar
        /// <summary>
        /// Executa uma Query SQL.
        /// </summary>
        /// <param name="comando">A Query SQL</param>
        /// <param name="parametros">Os parametros da Query</param>
        /// <returns>O objeto da primeira linha e primeira coluna resultante da Query</returns>
        public Object ExecuteInlineScalar(string comando, params DbParameter[] parametros)
        {
            Object result = null;
            DbCommand cmd = CreateInlineCommand(comando, parametros);
            result = cmd.ExecuteScalar();
            return result;
        }


        /// <summary>
        /// Executa uma Query SQL.
        /// </summary>
        /// <param name="comando">A Query SQL</param>
        /// <param name="parametros">Os parametros da Query</param>
        /// <returns>O objeto da primeira linha e primeira coluna resultante da Query</returns>
        public Object ExecuteScalar(string comando, params IDataAccessParameter[] parametros)
        {
            Object result = null;
            DbCommand cmd = CreateCommand(comando, parametros);
            result = cmd.ExecuteScalar();
            return result;
        }

        /// <summary>
        /// Executa uma Query SQL.
        /// </summary>
        /// <param name="comando">A Query SQL</param>
        /// <param name="parametros">Os parametros da Query</param>
        /// <returns>O objeto da primeira linha e primeira coluna resultante da Query</returns>
        public Object ExecuteFormatScalar(string comando, params object[] parametros)
        {
            Object result = null;
            DbCommand cmd = CreateFormatCommand(comando, parametros);
            result = cmd.ExecuteScalar();
            return result;
        } 
        #endregion

        #region Execute Scalar Generic
        /// <summary>
        /// Executa uma Query SQL.
        /// </summary>
        /// <param name="comando">A Query SQL</param>
        /// <param name="parametros">Os parametros da Query</param>
        /// <returns>O objeto da primeira linha e primeira coluna resultante da Query</returns>
        public T ExecuteInlineScalar<T>(string comando, params DbParameter[] parametros)
        {
            return (T)ExecuteInlineScalar(comando, parametros);
        }

        /// <summary>
        /// Executa uma Query SQL.
        /// </summary>
        /// <param name="comando">A Query SQL</param>
        /// <param name="parametros">Os parametros da Query</param>
        /// <returns>O objeto da primeira linha e primeira coluna resultante da Query</returns>
        public T ExecuteScalar<T>(string comando, params IDataAccessParameter[] parametros)
        {
            return (T)ExecuteScalar(comando, parametros);
        }


        /// <summary>
        /// Executa uma Query SQL.
        /// </summary>
        /// <param name="comando">A Query SQL</param>
        /// <param name="parametros">Os parametros da Query</param>
        /// <returns>O objeto da primeira linha e primeira coluna resultante da Query</returns>
        public T ExecuteFormatScalar<T>(string comando, params object[] parametros)
        {
            return (T)ExecuteFormatScalar(comando, parametros);
        } 
        #endregion

        #region Get Data Table
        /// <summary>
        /// Executa uma Query SQL.
        /// </summary>
        /// <param name="comando">A Query SQL</param>
        /// <param name="parametros">Os parametros da Query</param>
        /// <returns>Um DataTable com os resultados da Query</returns>
        public DataTable GetInlineDataTable(string comando, params DbParameter[] parametros)
        {
            DataTable result = new DataTable();
            DbDataAdapter adapter = Factory.CreateDataAdapter();
            DbCommand cmd = CreateInlineCommand(comando, parametros);
            adapter.SelectCommand = cmd;
            adapter.Fill(result);

            return result;
        }

        /// <summary>
        /// Executa uma Query SQL.
        /// </summary>
        /// <param name="comando">A Query SQL</param>
        /// <param name="parametros">Os parametros da Query</param>
        /// <returns>Um DataTable com os resultados da Query</returns>
        public DataTable GetDataTable(string comando, params IDataAccessParameter[] parametros)
        {
            DataTable result = new DataTable();
            DbDataAdapter adapter = Factory.CreateDataAdapter();
            DbCommand cmd = CreateCommand(comando, parametros);
            adapter.SelectCommand = cmd;
            adapter.Fill(result);

            return result;
        }

        /// <summary>
        /// Executa uma Query SQL.
        /// </summary>
        /// <param name="comando">A Query SQL</param>
        /// <param name="parametros">Os parametros da Query</param>
        /// <returns>Um DataTable com os resultados da Query</returns>
        public DataTable GetFormatDataTable(string comando, params object[] parametros)
        {
            DataTable result = new DataTable();
            DbDataAdapter adapter = Factory.CreateDataAdapter();
            DbCommand cmd = CreateFormatCommand(comando, parametros);
            adapter.SelectCommand = cmd;
            adapter.Fill(result);

            return result;
        } 
        #endregion

        public static DbType MapColumnType(Type originalType)
        {
            object val = Enum.Parse(typeof(DbType), originalType.Name);
            if (val != null)
                return (DbType)val;
            else
                return DbType.String;
        }


        public static string CommandFormat(string command, DbParameter[] parameterList)
        {
            if (command.Contains("{0}"))
            {
                object[] values = new object[parameterList.Length];
                for (int i = 0; i < parameterList.Length; i++)
                {
                    values[i] = parameterList[i].ParameterName;
                }

                command = String.Format(command, values);
            }
           


            return command;
        }

    }

}


